package com.topisv.tms.task.warehouseage;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import com.topisv.tms.entity.SkuOverInfo;

public class JdbcUtil {
	static protected Logger log = LoggerFactory.getLogger(JdbcUtil.class);
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
   static String DB_URL = "jdbc:mysql://localhost:3306/wms?useUnicode=true&characterEncoding=utf-8";

   //  Database credentials
   static String USER = "root";
   static String PASS = "paiuroot";

   static {
	   try {
		Class.forName("com.mysql.jdbc.Driver");
		Resource resource = new ClassPathResource("/application.properties");
		try {
			Properties props = PropertiesLoaderUtils.loadProperties(resource);
			DB_URL = props.getProperty("jdbc.url");
			USER = props.getProperty("jdbc.username");
			PASS = props.getProperty("jdbc.password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	    
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	}
   }
   public static List<SkuOverInfo> findByEndFlag(boolean endFlagP) {
	   print("jdbc find");
	   
	 //加入线程安全
	   List<SkuOverInfo> soiList = null;
	   SkuOverInfo soi = null;
	   Connection conn = null;
	   Statement stmt = null;
	   try{
	      conn = DriverManager.getConnection(DB_URL,USER,PASS);
	      stmt = conn.createStatement();
	      String sql;
	      sql = "SELECT count(1) totalNum FROM wa_sku_over_info where end_flag = 0 ";
	      ResultSet rs = stmt.executeQuery(sql);
	
	      long start = 0;
	      long limit = 50000L;
	      long totalNum = 0L;
	      while(rs.next()){
	         totalNum  = rs.getLong("totalNum");
	      }
	      soiList = Collections.synchronizedList(new ArrayList<SkuOverInfo>((int)totalNum));
	      long page = totalNum/limit;
		  if(totalNum/limit!=0){
			  page++;
		  }
		  List<QueryThread> threadList = new ArrayList<QueryThread>();
		  for(int i = 0 ; i < page;i++){
			  QueryThread t = new QueryThread(soiList,start,limit);
			  start = start + limit;
			  t.start();
			  threadList.add(t);
		  }
		  for (QueryThread t : threadList) {  
			  t.join(); 
		  }
		   System.out.println(soiList.size());
		   
	      rs.close();
	      stmt.close();
	      conn.close();
	     
	   }catch(SQLException se){
	      se.printStackTrace();
	      print("jdbc查询异常"+se);
	   }catch(Exception e){
	      e.printStackTrace();
	      print("jdbc查询异常"+e);
	   }finally{
	      try{
	         if(stmt!=null)
	            stmt.close();
	      }catch(SQLException se2){
	      }// nothing we can do
	      try{
	         if(conn!=null)
	            conn.close();
	      }catch(SQLException se){
	         se.printStackTrace();
	      }
	   }
	   return soiList;
	}
   
   
   public static void saveSkuOverInfo(List<SkuOverInfo> soiList) throws InterruptedException {
	   print("jdbc save");
	   int size = soiList.size();
	   int threadNum = size/50000;
	   if(size%50000!=0){
		   threadNum++;
	   }
	   System.out.println(threadNum);
	   List<SaveThread> threadList = new ArrayList<SaveThread>();
	   for(int i = 0 ; i < threadNum;i++){
			int startIndex = i*50000;
			int endIndex = startIndex+50000;
			if(endIndex>size){endIndex=size;}
			System.out.println(startIndex+"-"+endIndex);
			SaveThread t = new SaveThread(soiList,startIndex,endIndex);
			t.start();
			threadList.add(t);
		}
	   for (SaveThread t : threadList) {  
		   t.join(); 
       }
	   System.out.println("所有保存线程执行完毕!");
	}
   
   static class SaveThread extends Thread {
	   List<SkuOverInfo> soiList;
	   int startIndex;
	   int endIndex;
	   public SaveThread(List<SkuOverInfo> soiList,int startIndex,int endIndex){
		   this.soiList = soiList;
		   this.startIndex = startIndex;
		   this.endIndex = endIndex;
	   }
	   public void run(){
		   Connection conn = null;
		   PreparedStatement  pstmt=null;
		   PreparedStatement  pstmtu=null;
		   try{
		      conn = DriverManager.getConnection(DB_URL,USER,PASS);
		      conn.setAutoCommit(false);
		      String sql = "INSERT INTO wa_sku_over_info(warehouse_code,customer_code,sku,ope_time,start_time,end_time,over_num,over_day,end_Flag)"
		    		  + "VALUES(?,?,?,?,?,?,?,?,?)";
		      pstmt = conn.prepareStatement(sql);
		      
		      String sqlu = "UPDATE wa_sku_over_info  SET warehouse_code = ?,customer_code = ?,sku = ?,ope_time = ?,start_time = ?,end_time = ?,over_num = ? ,over_day = ?,end_Flag = ? "
		    		  + " WHERE	id = ? ";
		      pstmtu = conn.prepareStatement(sqlu);
		      
		      for(int q = startIndex ;q < endIndex ; q++){
		    	  SkuOverInfo soi = soiList.get(q);
		    	  if(soi.getId()==null){
		    		  pstmt.setString(1, soi.getWarehouseCode());
				      pstmt.setString(2, soi.getCustomerCode());
				      pstmt.setString(3, soi.getSku());
				      pstmt.setDate(4, new java.sql.Date(soi.getOpeTime().getTime()));
				      pstmt.setDate(5, new java.sql.Date(soi.getStartTime().getTime()));
				      pstmt.setDate(6, new java.sql.Date(soi.getEndTime().getTime()));
				      pstmt.setInt(7, soi.getOverNum());
				      pstmt.setInt(8, soi.getOverDay());
				      pstmt.setBoolean(9, soi.getEndFlag());
				      pstmt.addBatch();
		    	  }else {
		    		  pstmtu.setString(1, soi.getWarehouseCode());                       
		    		  pstmtu.setString(2, soi.getCustomerCode());                        
		    		  pstmtu.setString(3, soi.getSku());                                 
		    		  pstmtu.setDate(4, new java.sql.Date(soi.getOpeTime().getTime()));  
		    		  pstmtu.setDate(5, new java.sql.Date(soi.getStartTime().getTime()));
		    		  pstmtu.setDate(6, new java.sql.Date(soi.getEndTime().getTime()));  
		    		  pstmtu.setInt(7, soi.getOverNum());                                
		    		  pstmtu.setInt(8, soi.getOverDay());                                
		    		  pstmtu.setBoolean(9, soi.getEndFlag());                            
		    		  pstmtu.setLong(10, soi.getId());
		    		  pstmtu.addBatch();
		    	  }
		      }
		      pstmt.executeBatch();
		      pstmtu.executeBatch();
	    	  conn.commit();
	    	  print("提交"+startIndex+"-"+endIndex);
		   }catch(SQLException se){
		      //Handle errors for JDBC
		      se.printStackTrace();
		      print("jdbc保存异常"+se);
		   }catch(Exception e){
		      //Handle errors for Class.forName
		      e.printStackTrace();
		      print("jdbc保存异常"+e);
		   }finally{
		      try{
		         if(pstmt!=null)
		        	 pstmt.close();
		      }catch(SQLException se2){
		      }// nothing we can do
		      try{
		         if(conn!=null)
		            conn.close();
		      }catch(SQLException se){
		         se.printStackTrace();
		      }
		   }
	   }
   }
   
   
   
   static class QueryThread extends Thread {
	   long start;
	   long limit;
	   List<SkuOverInfo> soiList;
	   public QueryThread(List<SkuOverInfo> soiList,long start,long limit){
		    this.soiList = soiList;
			this.start = start;
			this.limit = limit;
	   }
	   public void run(){
//		   print("jdbc find"+(start+"-"+limit));
//		   List<SkuOverInfo> soiList = new ArrayList<SkuOverInfo>();
		   SkuOverInfo soi = null;
		   Connection conn = null;
		   Statement stmt = null;
		   try{
		      conn = DriverManager.getConnection(DB_URL,USER,PASS);
		      stmt = conn.createStatement();
		      String sql;
		      sql = "SELECT id,warehouse_code,customer_code,sku,ope_time,start_time,end_time,over_num,over_day,end_Flag FROM wa_sku_over_info where end_flag = 0 limit "+start+","+limit;
		      ResultSet rs = stmt.executeQuery(sql);
		
		      while(rs.next()){
		         Long id  = rs.getLong("id");
		         String warehouseCode = rs.getString("warehouse_code");
		         String customerCode = rs.getString("customer_code");
		         String sku = rs.getString("sku");
		         Date opeTime = rs.getDate("ope_time");
		         Date startTime = rs.getDate("start_time");
		         Date endTime = rs.getDate("end_time");
		         int overNum = rs.getInt("over_num");
		         int overDay = rs.getInt("over_day");
		         boolean endFlag = rs.getBoolean("end_Flag");
		         soi = new SkuOverInfo();
		         soi.setId(id);
		         soi.setWarehouseCode(warehouseCode);
		         soi.setCustomerCode(customerCode);
		         soi.setSku(sku);
		         soi.setOpeTime(opeTime);
		         soi.setStartTime(startTime);
		         soi.setEndTime(endTime);
		         soi.setOverNum(overNum);
		         soi.setOverDay(overDay);
		         soi.setEndFlag(endFlag);
		         soiList.add(soi);
		      }
		      rs.close();
		      stmt.close();
		      conn.close();
		     
		   }catch(SQLException se){
		      se.printStackTrace();
		      print("jdbc查询异常"+se);
		   }catch(Exception e){
		      e.printStackTrace();
		      print("jdbc查询异常"+e);
		   }finally{
		      try{
		         if(stmt!=null)
		            stmt.close();
		      }catch(SQLException se2){
		      }// nothing we can do
		      try{
		         if(conn!=null)
		            conn.close();
		      }catch(SQLException se){
		         se.printStackTrace();
		      }
		   }
		   
	   }
   }
   
//   public static void saveOrUpdateList(List<Object> objectList,List<Map<String,Object>> fieldSort,String insertSql ,String updateSql) {
//	   print("jdbc save");
//	   Connection conn = null;
//	   PreparedStatement  pstmt=null;
//	   
//	   PreparedStatement  pstmtu=null;
//	   try{
//	      //STEP 2: Register JDBC driver
//	      Class.forName("com.mysql.jdbc.Driver");
//	
//	      //STEP 3: Open a connection
////	      System.out.println("Connecting to database...");
//	      conn = DriverManager.getConnection(DB_URL,USER,PASS);
//	      conn.setAutoCommit(false);
//	      String sql = "INSERT INTO wa_sku_over_info(warehouse_code,customer_code,sku,ope_time,start_time,end_time,over_num,over_day,end_Flag)"
//	    		  + "VALUES(?,?,?,?,?,?,?,?,?)";
//	      pstmt = conn.prepareStatement(insertSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//	      
//	      String sqlu = "UPDATE wa_sku_over_info  SET warehouse_code = ?,customer_code = ?,sku = ?,ope_time = ?,start_time = ?,end_time = ?,over_num = ? ,over_day = ?,end_Flag = ? "
//	    		  + " WHERE	id = ? ";
//	      pstmtu = conn.prepareStatement(updateSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
//	      
//	      for(int q = 0 ;q < objectList.size();q++){
//	    	  Object soi = objectList.get(q);
//	    	  Class clazz = soi.getClass();
//	    	  for(int z = 0 ; z < fieldSort.size();z++) {
//	    		  Map<String,Object> param = fieldSort.get(z);
////	    		  Class methodClass = (Class)param.get("fieldClass");
////	    		  Object result = clazz.getMethod("get"+(String)param.get("fieldName"));
//	    		  String primaryKey = (String)param.get("primaryKey");
//	    		  Object result = clazz.getMethod("get"+(String)param.get("fieldName"));
//	    		  pstmt.setObject(z, result);
//	    		  if("Y".equals(primaryKey)){
//	    			  if(result==null){
//	    				  
//	    			  }else {
//	    				  
//	    			  }
//	    		  }
//	    	  }
//	    	 
//	    	  if(soi.getId()==null){
//	    		  pstmt.setString(1, soi.getWarehouseCode());
//			      pstmt.setString(2, soi.getCustomerCode());
//			      pstmt.setString(3, soi.getSku());
//			      pstmt.setDate(4, new java.sql.Date(soi.getOpeTime().getTime()));
//			      pstmt.setDate(5, new java.sql.Date(soi.getStartTime().getTime()));
//			      pstmt.setDate(6, new java.sql.Date(soi.getEndTime().getTime()));
//			      pstmt.setInt(7, soi.getOverNum());
//			      pstmt.setInt(8, soi.getOverDay());
//			      pstmt.setBoolean(9, soi.getEndFlag());
//	    	  }else {
//	    		  pstmtu.setString(1, soi.getWarehouseCode());                       
//	    		  pstmtu.setString(2, soi.getCustomerCode());                        
//	    		  pstmtu.setString(3, soi.getSku());                                 
//	    		  pstmtu.setDate(4, new java.sql.Date(soi.getOpeTime().getTime()));  
//	    		  pstmtu.setDate(5, new java.sql.Date(soi.getStartTime().getTime()));
//	    		  pstmtu.setDate(6, new java.sql.Date(soi.getEndTime().getTime()));  
//	    		  pstmtu.setInt(7, soi.getOverNum());                                
//	    		  pstmtu.setInt(8, soi.getOverDay());                                
//	    		  pstmtu.setBoolean(9, soi.getEndFlag());                            
//	    		  pstmtu.setLong(10, soi.getId());
//	    	  }
//		      
//		      if (q % 50000 == 0){
//		    	  pstmt.executeBatch();
//		    	  pstmtu.executeBatch();
//		    	  conn.commit();
//		      }
//	      }
//	      pstmt.executeBatch();
//	      pstmtu.executeBatch();
//    	  conn.commit();
//	   }catch(SQLException se){
//	      //Handle errors for JDBC
//	      se.printStackTrace();
//	      print("jdbc保存异常"+se);
//	   }catch(Exception e){
//	      //Handle errors for Class.forName
//	      e.printStackTrace();
//	      print("jdbc保存异常"+e);
//	   }finally{
//	      try{
//	         if(pstmt!=null)
//	        	 pstmt.close();
//	      }catch(SQLException se2){
//	      }// nothing we can do
//	      try{
//	         if(conn!=null)
//	            conn.close();
//	      }catch(SQLException se){
//	         se.printStackTrace();
//	      }
//	   }
//	}
   
   public static void print(String str){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String date = format.format(new java.util.Date());
		System.out.println(date+":"+str);
		log.info(date+":"+str);
	}
}
